package com.lovo.dao.impl;

import com.lovo.bean.*;
import com.lovo.dao.BaseDAO;
import com.lovo.dao.StudentDAO;

import javax.swing.tree.RowMapper;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl extends BaseDAO implements StudentDAO {
    @Override
    public Page<Student> findByPage(int current,String name,String className,String addr) {
        PreparedStatement statement = null;
        ResultSet result = null;
        User user = null;
        Page page = new Page();
        try {
            String selectSql = "select s.*,c.*,r.* ";
            String countSql = "select count(*) cnt ";
            String fromSql = "from t_student s left join t_class c on s.s_c_id=c.c_id left join t_room r on s.s_r_id=r.r_id ";
            String limitSql = " order by s_in_time desc limit ?,?";
            String whereSql = " where 1=1 ";
            List searchParams = new ArrayList();
            if(name != null && !"".equals(name)){
                whereSql += " and s.s_name like ?";
                searchParams.add("%"+name+"%");
            }
            if(className != null && !"".equals(className)){
                whereSql += " and c.c_name like ?";
                searchParams.add("%"+className+"%");
            }
            if(addr != null && !"".equals(addr)){
                whereSql += " and r.r_addr like ?";
                searchParams.add("%"+addr+"%");
            }
            statement = con.prepareStatement(selectSql + fromSql + whereSql + limitSql);
            for(int i = 0;i < searchParams.size();i++){
                statement.setObject(i+1,searchParams.get(i));
            }

            statement.setInt(searchParams.size() + 1,(current - 1) * page.getPageSize());
            statement.setInt(searchParams.size() + 2,page.getPageSize());
            result = statement.executeQuery();
            List students = new ArrayList<>();
            while(result.next()){
                Student student = fillStudent(result);
                students.add(student);
            }
            page.setList(students);

            statement = con.prepareStatement(countSql + fromSql + whereSql);
            for(int i = 0;i < searchParams.size();i++){
                statement.setObject(i+1,searchParams.get(i));
            }
            result = statement.executeQuery();
            result.next();
            page.setCount(result.getInt("cnt"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(con,statement,result);
        }
        return page;
    }

    @Override
    public Student findById(int id) {
        PreparedStatement statement = null;
        ResultSet result = null;
        Student student = null;
        try {
            String sql = "select s.*,c.*,r.* " +
                    "from t_student s left join t_class c on s.s_c_id=c.c_id " +
                    "left join t_room r on s.s_r_id=r.r_id where s_id=?";

            statement = con.prepareStatement(sql);
            statement.setInt(1,id);
            result = statement.executeQuery();
            if(result.next()){
                student = fillStudent(result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(con,statement,result);
        }
        return student;
    }

    @Override
    public int insert(Student student) {
        PreparedStatement statement = null;
        int result = 0;
        try {
            String sql = "insert into t_student(s_name,s_gender,s_in_time,s_phone,s_head_img,s_c_id,s_r_id)" +
                    " values(?,?,?,?,?,?,?)";
            statement = con.prepareStatement(sql);
            statement.setString(1,student.getName());
            statement.setString(2,student.getGender());
            statement.setString(3,student.getInTime().toString());
            statement.setString(4,student.getPhone());
            statement.setString(5,student.getHeadImg());
            statement.setInt(6,student.getClassz().getId());
            statement.setInt(7,student.getRoom().getId());
            result = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(con,statement);
        }
        return result;
    }

    @Override
    public int update(Student student) {
        PreparedStatement statement = null;
        int result = 0;
        try {
            String sql = "update t_student set s_r_id=? where s_id=?";
            statement = con.prepareStatement(sql);
            if(student.getRoom() == null){
                statement.setNull(1, Types.INTEGER);
            }else{
                statement.setInt(1,student.getRoom().getId());
            }

            statement.setInt(2,student.getId());
            result = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(con,statement);
        }
        return result;
    }

    private Student fillStudent(ResultSet result) throws SQLException {
        Student student = new Student();
        student.setId(result.getInt("s_id"));
        student.setName(result.getString("s_name"));
        student.setGender(result.getString("s_gender"));
        student.setPhone(result.getString("s_phone"));
        student.setHeadImg(result.getString("s_head_img"));

        Classz classz = new Classz();
        classz.setId(result.getInt("c_id"));
        classz.setName(result.getString("c_name"));
        student.setClassz(classz);

        Room room = new Room();
        room.setId(result.getInt("r_id"));
        room.setAddr(result.getString("r_addr"));
        student.setRoom(room);

        return student;
    }

    public static void main(String[] args) {
        StudentDAO studentDAO = new StudentDAOImpl();
//        System.out.println(studentDAO.findByPage(1,"","j194",""));
        Student student = new Student();
        student.setName("小王");
        student.setGender("男");
        student.setInTime(LocalDate.now());
        student.setClassz(new Classz(1));
        student.setRoom(new Room(1));
        studentDAO.insert(student);
    }
}
